/*
Deployment script for CID.Database
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "CID.UnitTests"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\"

GO
USE [master]

GO
:on error exit
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [CID], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', FILEGROWTH = 1024 KB)
    LOG ON (NAME = [CID_log], FILENAME = '$(DefaultLogPath)$(DatabaseName)_log.ldf', MAXSIZE = 2097152 MB, FILEGROWTH = 10 %) COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXECUTE sp_dbcmptlevel [$(DatabaseName)], 100;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET HONOR_BROKER_PRIORITY OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
USE [$(DatabaseName)]

GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.	
 Use SQLCMD syntax to include a file in the pre-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO
PRINT N'Creating [dbo].[ActiveVersions]...';


GO
CREATE TABLE [dbo].[ActiveVersions] (
    [Id]             INT IDENTITY (1, 1) NOT NULL,
    [Version_id]     INT NOT NULL,
    [Environment_id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);


GO
PRINT N'Creating [dbo].[Components]...';


GO
CREATE TABLE [dbo].[Components] (
    [Id]             INT             IDENTITY (1, 1) NOT NULL,
    [Name]           NVARCHAR (255)  NOT NULL,
    [Description]    NVARCHAR (1024) NOT NULL,
    [Enabled]        BIT             NOT NULL,
    [ServerRole_id]  INT             NOT NULL,
    [Responsible_id] INT             NOT NULL
);


GO
PRINT N'Creating PK_Components...';


GO
ALTER TABLE [dbo].[Components]
    ADD CONSTRAINT [PK_Components] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Definitions]...';


GO
CREATE TABLE [dbo].[Definitions] (
    [Id]                      INT           IDENTITY (1, 1) NOT NULL,
    [Type]                    NVARCHAR (25) NOT NULL,
    [Component_id]            INT           NULL,
    [ActivationTemplate_id]   INT           NULL,
    [DeactivationTemplate_id] INT           NULL,
    [Version_id]              INT           NULL,
    [DeploymentTemplate_id]   INT           NULL
);


GO
PRINT N'Creating PK_Definitions...';


GO
ALTER TABLE [dbo].[Definitions]
    ADD CONSTRAINT [PK_Definitions] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[DeploymentAgents]...';


GO
CREATE TABLE [dbo].[DeploymentAgents] (
    [Id]      INT            IDENTITY (1, 1) NOT NULL,
    [Name]    NVARCHAR (255) NOT NULL,
    [Address] NVARCHAR (255) NOT NULL,
    [Enabled] BIT            NOT NULL
);


GO
PRINT N'Creating PK_DeploymentAgents...';


GO
ALTER TABLE [dbo].[DeploymentAgents]
    ADD CONSTRAINT [PK_DeploymentAgents] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Environments]...';


GO
CREATE TABLE [dbo].[Environments] (
    [Id]             INT             IDENTITY (1, 1) NOT NULL,
    [Name]           NVARCHAR (255)  NOT NULL,
    [Description]    NVARCHAR (1024) NOT NULL,
    [Enabled]        BIT             NOT NULL,
    [IsProduction]   BIT             NOT NULL,
    [Responsible_id] INT             NOT NULL
);


GO
PRINT N'Creating PK_Environments...';


GO
ALTER TABLE [dbo].[Environments]
    ADD CONSTRAINT [PK_Environments] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Locations]...';


GO
CREATE TABLE [dbo].[Locations] (
    [Id]                 INT             IDENTITY (1, 1) NOT NULL,
    [Name]               NVARCHAR (255)  NOT NULL,
    [Description]        NVARCHAR (1024) NOT NULL,
    [DeploymentAgent_id] INT             NOT NULL
);


GO
PRINT N'Creating PK_Locations...';


GO
ALTER TABLE [dbo].[Locations]
    ADD CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Packages]...';


GO
CREATE TABLE [dbo].[Packages] (
    [Id]             INT             IDENTITY (1, 1) NOT NULL,
    [Content]        VARBINARY (MAX) NULL,
	[Comment]        VARCHAR (1024)  NOT NULL,
    [PackageType_id] INT             NOT NULL,
    [Version_id]     INT             NOT NULL
);


GO
PRINT N'Creating PK_Packages...';


GO
ALTER TABLE [dbo].[Packages]
    ADD CONSTRAINT [PK_Packages] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PackageTypes]...';


GO
CREATE TABLE [dbo].[PackageTypes] (
    [Id]          INT             IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (255)  NOT NULL,
    [Description] NVARCHAR (1024) NOT NULL,
    [Enabled]     BIT             NOT NULL
);


GO
PRINT N'Creating PK_PackageTypes...';


GO
ALTER TABLE [dbo].[PackageTypes]
    ADD CONSTRAINT [PK_PackageTypes] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[ParameterDeploymentLogs]...';


GO
CREATE TABLE [dbo].[ParameterDeploymentLogs] (
    [Id]                      INT            IDENTITY (1, 1) NOT NULL,
    [Type]                    NVARCHAR (20)  NOT NULL,
    [ParameterName]           NVARCHAR (255) NOT NULL,
    [ParameterValue]          NVARCHAR (MAX) NOT NULL,
    [VersionDeploymentLog_id] INT            NOT NULL
);


GO
PRINT N'Creating PK_ParameterDeploymentLogs...';


GO
ALTER TABLE [dbo].[ParameterDeploymentLogs]
    ADD CONSTRAINT [PK_ParameterDeploymentLogs] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Parameters]...';


GO
CREATE TABLE [dbo].[Parameters] (
    [Id]          INT             IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (255)  NOT NULL,
    [Description] NVARCHAR (1024) NOT NULL,
    [Type]        NVARCHAR (255)  NOT NULL,
    [Template_id] INT             NOT NULL
);


GO
PRINT N'Creating PK_Parameters...';


GO
ALTER TABLE [dbo].[Parameters]
    ADD CONSTRAINT [PK_Parameters] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[ParameterValues]...';


GO
CREATE TABLE [dbo].[ParameterValues] (
    [Id]              INT            IDENTITY (1, 1) NOT NULL,
    [Type]            NVARCHAR (20)  NOT NULL,
    [SerializedValue] NVARCHAR (MAX) NOT NULL,
    [Definition_id]   INT            NOT NULL,
    [Parameter_id]    INT            NOT NULL,
    [Environment_id]  INT            NULL
);


GO
PRINT N'Creating PK_ParameterValues...';


GO
ALTER TABLE [dbo].[ParameterValues]
    ADD CONSTRAINT [PK_ParameterValues] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[ReleaseDeploymentLogs]...';


GO
CREATE TABLE [dbo].[ReleaseDeploymentLogs] (
    [Id]			             INT              IDENTITY (1, 1) NOT NULL,
	[DeploymentUniqueIdentifier] UNIQUEIDENTIFIER NOT NULL,
    [Details]                    NVARCHAR (MAX)   NOT NULL,
    [StartTimestamp]             DATETIME         NULL,
    [EndTimestamp]               DATETIME         NULL,
    [Status]                     NVARCHAR (255)   NOT NULL,
    [Environment_id]             INT              NOT NULL,
    [Release_id]                 INT              NOT NULL,
    [Responsible_id]             INT              NOT NULL
);


GO
PRINT N'Creating PK_ReleaseDeploymentLogs...';


GO
ALTER TABLE [dbo].[ReleaseDeploymentLogs]
    ADD CONSTRAINT [PK_ReleaseDeploymentLogs] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Releases]...';


GO
CREATE TABLE [dbo].[Releases] (
    [Id]             INT             IDENTITY (1, 1) NOT NULL,
    [Name]           NVARCHAR (255)  NOT NULL,
    [Description]    NVARCHAR (1024) NOT NULL,
    [Status]         NVARCHAR (255)  NOT NULL,
    [Responsible_id] INT             NOT NULL
);


GO
PRINT N'Creating PK_Releases...';


GO
ALTER TABLE [dbo].[Releases]
    ADD CONSTRAINT [PK_Releases] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[ServerAssignments]...';


GO
CREATE TABLE [dbo].[ServerAssignments] (
    [Id]             INT IDENTITY (1, 1) NOT NULL,
    [Environment_id] INT NOT NULL,
    [Server_id]      INT NOT NULL,
    [ServerRole_id]  INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);


GO
PRINT N'Creating [dbo].[ServerRoles]...';


GO
CREATE TABLE [dbo].[ServerRoles] (
    [Id]          INT             IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (255)  NOT NULL,
    [Description] NVARCHAR (1024) NOT NULL,
    [Enabled]     BIT             NOT NULL
);


GO
PRINT N'Creating PK_ServerRoles...';


GO
ALTER TABLE [dbo].[ServerRoles]
    ADD CONSTRAINT [PK_ServerRoles] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Servers]...';


GO
CREATE TABLE [dbo].[Servers] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (255) NOT NULL,
    [Address]     NVARCHAR (255) NOT NULL,
    [Enabled]     BIT            NOT NULL,
    [Location_id] INT            NOT NULL
);


GO
PRINT N'Creating PK_Servers...';


GO
ALTER TABLE [dbo].[Servers]
    ADD CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[Templates]...';


GO
CREATE TABLE [dbo].[Templates] (
    [Id]                     INT             IDENTITY (1, 1) NOT NULL,
    [Type]                   NVARCHAR (20)   NOT NULL,
    [Name]                   NVARCHAR (255)  NOT NULL,
    [Description]            NVARCHAR (1024) NOT NULL,
    [Enabled]                BIT             NOT NULL,
	[MultipleServersSupport] BIT             NOT NULL,
    [Content]                VARBINARY (MAX) NULL,
    [TemplateType_id]        INT             NOT NULL
);


GO
PRINT N'Creating PK_Templates...';


GO
ALTER TABLE [dbo].[Templates]
    ADD CONSTRAINT [PK_Templates] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[TemplateTypes]...';


GO
CREATE TABLE [dbo].[TemplateTypes] (
    [Id]                                 INT             IDENTITY (1, 1) NOT NULL,
    [Name]                               NVARCHAR (255)  NOT NULL,
    [Description]                        NVARCHAR (1024) NOT NULL,
	[TemplateContentEditorConverterType] NVARCHAR (255)  NOT NULL,
	[TemplateContentEditorViewName]      NVARCHAR (255)  NOT NULL,
    [Enabled]                            BIT             NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);


GO
PRINT N'Creating [dbo].[UserRoleAssignments]...';


GO
CREATE TABLE [dbo].[UserRoleAssignments] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [UserRole] NVARCHAR (255) NOT NULL,
    [User_id]  INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);


GO
PRINT N'Creating [dbo].[Users]...';


GO
CREATE TABLE [dbo].[Users] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Name]     NVARCHAR (255) NOT NULL,
    [UserName] NVARCHAR (255) NOT NULL,
    [Email]    NVARCHAR (255) NOT NULL,
    [Enabled]  BIT            NOT NULL
);


GO
PRINT N'Creating PK_Users...';


GO
ALTER TABLE [dbo].[Users]
    ADD CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[VersionComponentDependencies]...';


GO
CREATE TABLE [dbo].[VersionComponentDependencies] (
    [Component_id] INT NOT NULL,
    [Version_id]   INT NOT NULL
);


GO
PRINT N'Creating [dbo].[VersionDeploymentLogs]...';


GO
CREATE TABLE [dbo].[VersionDeploymentLogs] (
    [Id]                      INT IDENTITY (1, 1) NOT NULL,
    [ReleaseDeploymentLog_id] INT NOT NULL,
    [Version_id]              INT NOT NULL,
    [ActivationTemplate_id]   INT NOT NULL,
    [DeactivationTemplate_id] INT NOT NULL,
    [DeploymentTemplate_id]   INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);


GO
PRINT N'Creating [dbo].[Versions]...';


GO
CREATE TABLE [dbo].[Versions] (
    [Id]               INT             IDENTITY (1, 1) NOT NULL,
    [Name]             NVARCHAR (255)  NOT NULL,
    [Description]      NVARCHAR (1024) NOT NULL,
    [Component_id]     INT             NOT NULL,
    [Release_id]       INT             NULL,
    [ActivePackage_id] INT             NULL
);


GO
PRINT N'Creating PK_Versions...';


GO
ALTER TABLE [dbo].[Versions]
    ADD CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating FK_ActiveVersion_Environment...';


GO
ALTER TABLE [dbo].[ActiveVersions] WITH NOCHECK
    ADD CONSTRAINT [FK_ActiveVersion_Environment] FOREIGN KEY ([Environment_id]) REFERENCES [dbo].[Environments] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ActiveVersion_Version...';


GO
ALTER TABLE [dbo].[ActiveVersions] WITH NOCHECK
    ADD CONSTRAINT [FK_ActiveVersion_Version] FOREIGN KEY ([Version_id]) REFERENCES [dbo].[Versions] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Component_ServerRole...';


GO
ALTER TABLE [dbo].[Components] WITH NOCHECK
    ADD CONSTRAINT [FK_Component_ServerRole] FOREIGN KEY ([ServerRole_id]) REFERENCES [dbo].[ServerRoles] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Component_User...';


GO
ALTER TABLE [dbo].[Components] WITH NOCHECK
    ADD CONSTRAINT [FK_Component_User] FOREIGN KEY ([Responsible_id]) REFERENCES [dbo].[Users] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ActivationDeactivationDefinition_ActivationTemplate...';


GO
ALTER TABLE [dbo].[Definitions] WITH NOCHECK
    ADD CONSTRAINT [FK_ActivationDeactivationDefinition_ActivationTemplate] FOREIGN KEY ([ActivationTemplate_id]) REFERENCES [dbo].[Templates] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ActivationDeactivationDefinition_Component...';


GO
ALTER TABLE [dbo].[Definitions] WITH NOCHECK
    ADD CONSTRAINT [FK_ActivationDeactivationDefinition_Component] FOREIGN KEY ([Component_id]) REFERENCES [dbo].[Components] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ActivationDeactivationDefinition_DeactivationTemplate...';


GO
ALTER TABLE [dbo].[Definitions] WITH NOCHECK
    ADD CONSTRAINT [FK_ActivationDeactivationDefinition_DeactivationTemplate] FOREIGN KEY ([DeactivationTemplate_id]) REFERENCES [dbo].[Templates] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_DeploymentDefinition_DeploymentTemplate...';


GO
ALTER TABLE [dbo].[Definitions] WITH NOCHECK
    ADD CONSTRAINT [FK_DeploymentDefinition_DeploymentTemplate] FOREIGN KEY ([DeploymentTemplate_id]) REFERENCES [dbo].[Templates] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_DeploymentDefinition_Version...';


GO
ALTER TABLE [dbo].[Definitions] WITH NOCHECK
    ADD CONSTRAINT [FK_DeploymentDefinition_Version] FOREIGN KEY ([Version_id]) REFERENCES [dbo].[Versions] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Environment_User...';


GO
ALTER TABLE [dbo].[Environments] WITH NOCHECK
    ADD CONSTRAINT [FK_Environment_User] FOREIGN KEY ([Responsible_id]) REFERENCES [dbo].[Users] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Location_DeploymentAgent...';


GO
ALTER TABLE [dbo].[Locations] WITH NOCHECK
    ADD CONSTRAINT [FK_Location_DeploymentAgent] FOREIGN KEY ([DeploymentAgent_id]) REFERENCES [dbo].[DeploymentAgents] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Package_PackageType...';


GO
ALTER TABLE [dbo].[Packages] WITH NOCHECK
    ADD CONSTRAINT [FK_Package_PackageType] FOREIGN KEY ([PackageType_id]) REFERENCES [dbo].[PackageTypes] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Package_Version...';


GO
ALTER TABLE [dbo].[Packages] WITH NOCHECK
    ADD CONSTRAINT [FK_Package_Version] FOREIGN KEY ([Version_id]) REFERENCES [dbo].[Versions] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ParameterDeploymentLog_VersionDeploymentLog...';


GO
ALTER TABLE [dbo].[ParameterDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_ParameterDeploymentLog_VersionDeploymentLog] FOREIGN KEY ([VersionDeploymentLog_id]) REFERENCES [dbo].[VersionDeploymentLogs] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Parameter_Template...';


GO
ALTER TABLE [dbo].[Parameters] WITH NOCHECK
    ADD CONSTRAINT [FK_Parameter_Template] FOREIGN KEY ([Template_id]) REFERENCES [dbo].[Templates] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_EnvironmentParameterValue_Environment...';


GO
ALTER TABLE [dbo].[ParameterValues] WITH NOCHECK
    ADD CONSTRAINT [FK_EnvironmentParameterValue_Environment] FOREIGN KEY ([Environment_id]) REFERENCES [dbo].[Environments] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ParameterValue_Definition...';


GO
ALTER TABLE [dbo].[ParameterValues] WITH NOCHECK
    ADD CONSTRAINT [FK_ParameterValue_Definition] FOREIGN KEY ([Definition_id]) REFERENCES [dbo].[Definitions] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ParameterValue_Parameter...';


GO
ALTER TABLE [dbo].[ParameterValues] WITH NOCHECK
    ADD CONSTRAINT [FK_ParameterValue_Parameter] FOREIGN KEY ([Parameter_id]) REFERENCES [dbo].[Parameters] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ReleaseDeploymentLog_Environment...';


GO
ALTER TABLE [dbo].[ReleaseDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_ReleaseDeploymentLog_Environment] FOREIGN KEY ([Environment_id]) REFERENCES [dbo].[Environments] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ReleaseDeploymentLog_Release...';


GO
ALTER TABLE [dbo].[ReleaseDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_ReleaseDeploymentLog_Release] FOREIGN KEY ([Release_id]) REFERENCES [dbo].[Releases] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ReleaseDeploymentLog_User...';


GO
ALTER TABLE [dbo].[ReleaseDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_ReleaseDeploymentLog_User] FOREIGN KEY ([Responsible_id]) REFERENCES [dbo].[Users] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Release_User...';


GO
ALTER TABLE [dbo].[Releases] WITH NOCHECK
    ADD CONSTRAINT [FK_Release_User] FOREIGN KEY ([Responsible_id]) REFERENCES [dbo].[Users] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ServerAssignment_Environment...';


GO
ALTER TABLE [dbo].[ServerAssignments] WITH NOCHECK
    ADD CONSTRAINT [FK_ServerAssignment_Environment] FOREIGN KEY ([Environment_id]) REFERENCES [dbo].[Environments] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ServerAssignment_Server...';


GO
ALTER TABLE [dbo].[ServerAssignments] WITH NOCHECK
    ADD CONSTRAINT [FK_ServerAssignment_Server] FOREIGN KEY ([Server_id]) REFERENCES [dbo].[Servers] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_ServerAssignment_ServerRole...';


GO
ALTER TABLE [dbo].[ServerAssignments] WITH NOCHECK
    ADD CONSTRAINT [FK_ServerAssignment_ServerRole] FOREIGN KEY ([ServerRole_id]) REFERENCES [dbo].[ServerRoles] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Server_Location...';


GO
ALTER TABLE [dbo].[Servers] WITH NOCHECK
    ADD CONSTRAINT [FK_Server_Location] FOREIGN KEY ([Location_id]) REFERENCES [dbo].[Locations] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Template_TemplateType...';


GO
ALTER TABLE [dbo].[Templates] WITH NOCHECK
    ADD CONSTRAINT [FK_Template_TemplateType] FOREIGN KEY ([TemplateType_id]) REFERENCES [dbo].[TemplateTypes] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_UserRoleAssignment_User...';


GO
ALTER TABLE [dbo].[UserRoleAssignments] WITH NOCHECK
    ADD CONSTRAINT [FK_UserRoleAssignment_User] FOREIGN KEY ([User_id]) REFERENCES [dbo].[Users] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_VersionComponentDependency_Component...';


GO
ALTER TABLE [dbo].[VersionComponentDependencies] WITH NOCHECK
    ADD CONSTRAINT [FK_VersionComponentDependency_Component] FOREIGN KEY ([Component_id]) REFERENCES [dbo].[Components] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_VersionComponentDependency_Version...';


GO
ALTER TABLE [dbo].[VersionComponentDependencies] WITH NOCHECK
    ADD CONSTRAINT [FK_VersionComponentDependency_Version] FOREIGN KEY ([Version_id]) REFERENCES [dbo].[Versions] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_VersionDeploymentLog_ActivationTemplate...';


GO
ALTER TABLE [dbo].[VersionDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_VersionDeploymentLog_ActivationTemplate] FOREIGN KEY ([ActivationTemplate_id]) REFERENCES [dbo].[Templates] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_VersionDeploymentLog_DeactivationTemplate...';


GO
ALTER TABLE [dbo].[VersionDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_VersionDeploymentLog_DeactivationTemplate] FOREIGN KEY ([DeactivationTemplate_id]) REFERENCES [dbo].[Templates] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_VersionDeploymentLog_DeploymentTemplate...';


GO
ALTER TABLE [dbo].[VersionDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_VersionDeploymentLog_DeploymentTemplate] FOREIGN KEY ([DeploymentTemplate_id]) REFERENCES [dbo].[Templates] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_VersionDeploymentLog_ReleaseDeploymentLog...';


GO
ALTER TABLE [dbo].[VersionDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_VersionDeploymentLog_ReleaseDeploymentLog] FOREIGN KEY ([ReleaseDeploymentLog_id]) REFERENCES [dbo].[ReleaseDeploymentLogs] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_VersionDeploymentLog_Version...';


GO
ALTER TABLE [dbo].[VersionDeploymentLogs] WITH NOCHECK
    ADD CONSTRAINT [FK_VersionDeploymentLog_Version] FOREIGN KEY ([Version_id]) REFERENCES [dbo].[Versions] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Version_Component...';


GO
ALTER TABLE [dbo].[Versions] WITH NOCHECK
    ADD CONSTRAINT [FK_Version_Component] FOREIGN KEY ([Component_id]) REFERENCES [dbo].[Components] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Version_Release...';


GO
ALTER TABLE [dbo].[Versions] WITH NOCHECK
    ADD CONSTRAINT [FK_Version_Release] FOREIGN KEY ([Release_id]) REFERENCES [dbo].[Releases] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_Version_ActivePackage...';


GO
ALTER TABLE [dbo].[Versions]
    ADD CONSTRAINT [FK_Version_ActivePackage] FOREIGN KEY ([ActivePackage_id]) REFERENCES [dbo].[Packages] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;

GO

-- Refactoring step to update target server with deployed transaction logs
CREATE TABLE  [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
GO
sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
GO

GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO
PRINT N'Checking existing data against newly created constraints';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [dbo].[ActiveVersions] WITH CHECK CHECK CONSTRAINT [FK_ActiveVersion_Environment];

ALTER TABLE [dbo].[ActiveVersions] WITH CHECK CHECK CONSTRAINT [FK_ActiveVersion_Version];

ALTER TABLE [dbo].[Components] WITH CHECK CHECK CONSTRAINT [FK_Component_ServerRole];

ALTER TABLE [dbo].[Components] WITH CHECK CHECK CONSTRAINT [FK_Component_User];

ALTER TABLE [dbo].[Definitions] WITH CHECK CHECK CONSTRAINT [FK_ActivationDeactivationDefinition_ActivationTemplate];

ALTER TABLE [dbo].[Definitions] WITH CHECK CHECK CONSTRAINT [FK_ActivationDeactivationDefinition_Component];

ALTER TABLE [dbo].[Definitions] WITH CHECK CHECK CONSTRAINT [FK_ActivationDeactivationDefinition_DeactivationTemplate];

ALTER TABLE [dbo].[Definitions] WITH CHECK CHECK CONSTRAINT [FK_DeploymentDefinition_DeploymentTemplate];

ALTER TABLE [dbo].[Definitions] WITH CHECK CHECK CONSTRAINT [FK_DeploymentDefinition_Version];

ALTER TABLE [dbo].[Environments] WITH CHECK CHECK CONSTRAINT [FK_Environment_User];

ALTER TABLE [dbo].[Locations] WITH CHECK CHECK CONSTRAINT [FK_Location_DeploymentAgent];

ALTER TABLE [dbo].[Packages] WITH CHECK CHECK CONSTRAINT [FK_Package_PackageType];

ALTER TABLE [dbo].[Packages] WITH CHECK CHECK CONSTRAINT [FK_Package_Version];

ALTER TABLE [dbo].[ParameterDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_ParameterDeploymentLog_VersionDeploymentLog];

ALTER TABLE [dbo].[Parameters] WITH CHECK CHECK CONSTRAINT [FK_Parameter_Template];

ALTER TABLE [dbo].[ParameterValues] WITH CHECK CHECK CONSTRAINT [FK_EnvironmentParameterValue_Environment];

ALTER TABLE [dbo].[ParameterValues] WITH CHECK CHECK CONSTRAINT [FK_ParameterValue_Definition];

ALTER TABLE [dbo].[ParameterValues] WITH CHECK CHECK CONSTRAINT [FK_ParameterValue_Parameter];

ALTER TABLE [dbo].[ReleaseDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_ReleaseDeploymentLog_Environment];

ALTER TABLE [dbo].[ReleaseDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_ReleaseDeploymentLog_Release];

ALTER TABLE [dbo].[ReleaseDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_ReleaseDeploymentLog_User];

ALTER TABLE [dbo].[Releases] WITH CHECK CHECK CONSTRAINT [FK_Release_User];

ALTER TABLE [dbo].[ServerAssignments] WITH CHECK CHECK CONSTRAINT [FK_ServerAssignment_Environment];

ALTER TABLE [dbo].[ServerAssignments] WITH CHECK CHECK CONSTRAINT [FK_ServerAssignment_Server];

ALTER TABLE [dbo].[ServerAssignments] WITH CHECK CHECK CONSTRAINT [FK_ServerAssignment_ServerRole];

ALTER TABLE [dbo].[Servers] WITH CHECK CHECK CONSTRAINT [FK_Server_Location];

ALTER TABLE [dbo].[Templates] WITH CHECK CHECK CONSTRAINT [FK_Template_TemplateType];

ALTER TABLE [dbo].[UserRoleAssignments] WITH CHECK CHECK CONSTRAINT [FK_UserRoleAssignment_User];

ALTER TABLE [dbo].[VersionComponentDependencies] WITH CHECK CHECK CONSTRAINT [FK_VersionComponentDependency_Component];

ALTER TABLE [dbo].[VersionComponentDependencies] WITH CHECK CHECK CONSTRAINT [FK_VersionComponentDependency_Version];

ALTER TABLE [dbo].[VersionDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_VersionDeploymentLog_ActivationTemplate];

ALTER TABLE [dbo].[VersionDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_VersionDeploymentLog_DeactivationTemplate];

ALTER TABLE [dbo].[VersionDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_VersionDeploymentLog_DeploymentTemplate];

ALTER TABLE [dbo].[VersionDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_VersionDeploymentLog_ReleaseDeploymentLog];

ALTER TABLE [dbo].[VersionDeploymentLogs] WITH CHECK CHECK CONSTRAINT [FK_VersionDeploymentLog_Version];

ALTER TABLE [dbo].[Versions] WITH CHECK CHECK CONSTRAINT [FK_Version_Component];

ALTER TABLE [dbo].[Versions] WITH CHECK CHECK CONSTRAINT [FK_Version_Release];


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        DECLARE @VarDecimalSupported AS BIT;
        SELECT @VarDecimalSupported = 0;
        IF ((ServerProperty(N'EngineEdition') = 3)
            AND (((@@microsoftversion / power(2, 24) = 9)
                  AND (@@microsoftversion & 0xffff >= 3024))
                 OR ((@@microsoftversion / power(2, 24) = 10)
                     AND (@@microsoftversion & 0xffff >= 1600))))
            SELECT @VarDecimalSupported = 1;
        IF (@VarDecimalSupported > 0)
            BEGIN
                EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
            END
    END


GO
ALTER DATABASE [$(DatabaseName)]
    SET MULTI_USER 
    WITH ROLLBACK IMMEDIATE;


GO
